/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.AlertMessage;
import com.elcom.emocbc.dbmapping.tables.daos.AlertMessageDao;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class AlertMessagePersistance extends AlertMessageDao {

    private final AlertMessage ALERT_MESSAGE = AlertMessage.ALERT_MESSAGE;

    public AlertMessagePersistance() throws SQLException {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<com.elcom.emocbc.dbmapping.tables.pojos.AlertMessage> findByCreatedTime(Timestamp createdTime) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(ALERT_MESSAGE)
                    .where(ALERT_MESSAGE.CREATED_TIME.greaterOrEqual(createdTime)
                    .and(ALERT_MESSAGE.STATUS.eq("1")))
                    .fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.AlertMessage.class);
        } finally {
            DbAccess.commitTransaction(con);
            DbAccess.closeConnection(con);
        }
    }
    
    public List<com.elcom.emocbc.dbmapping.tables.pojos.AlertMessage> findByStatus(String status) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(ALERT_MESSAGE)
                    .where(ALERT_MESSAGE.STATUS.eq(status))
                    .fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.AlertMessage.class);
        } finally {
            DbAccess.commitTransaction(con);
            DbAccess.closeConnection(con);
        }
    }
    
    public int updateStatus(String status, Long id) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.update(ALERT_MESSAGE)
                    .set(ALERT_MESSAGE.STATUS, status)
                    .where(ALERT_MESSAGE.ID.eq(id))
                    .execute();
        } finally {
            DbAccess.commitTransaction(con);
            DbAccess.closeConnection(con);
        }
    }
}
